package com.itheima.health.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.itheima.health.pojo.Member;
import com.itheima.health.vo.ReportVo;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;
import java.util.Map;

/**
 * @author 十一
 * @Description
 * @create 2020-11-12-20:38
 */
public interface ReportMapper extends BaseMapper<Member> {

    @Select("select  id,PHONENUMBER  from t_member where PHONENUMBER = #{telephone}")
    Member findMemberByTelephone(@Param("telephone") String telephone);

    //会员占比
    @Select(" SELECT (CASE  when m.age<=0 then '年龄非法' " +
            "WHEN m.age BETWEEN 1 AND 18 THEN '0-18岁' " +
            "              WHEN m.age BETWEEN 19 AND 30 THEN '18-30岁' " +
            "              WHEN m.age BETWEEN 31 AND 45 THEN '30-45岁' " +
            "              WHEN m.age > 45 THEN '45岁以上'  " +
            "              end ) as name ,COUNT(1) as  'value'  " +
            "              FROM  " +
            "              (SELECT NOW() AS '当前系统时间',birthday,TIMESTAMPDIFF(YEAR, birthday, CURDATE()) as age  from t_member ) as m  " +
            "              GROUP BY name")
    List<Map> findMemberProportionByAge();

    @Select(" SELECT (case " +
            "  when sex=1 then '女' else '男' end ) as name, " +
            " count(id) as value " +
            " from t_member  " +
            " GROUP BY sex")
    List<Map> findMemberProportionBySex();



    @Select("select count(ts.id) as value ,ts.name  " +
            "from   t_order tor, t_setmeal ts " +
            "where  tor.setmeal_id  = ts.id " +
            "group by ts.id")
    List<Map> setmealProportion();


//    @Select(" select COUNT(tm.id) as count , CONCAT(rpt.fyear,'-',rpt.fmonth) as yearsandmonth " +
//            " from t_member tm , rpt_date rpt " +
//            " where rpt.fdate = tm.REGTIME " +
//            " and rpt.fyear = #{year} " +
//            " and rpt.fmonth  BETWEEN #{monthfirst} and  #{monthend} " +
//            " GROUP BY rpt.fmonth ")
//    List<Map> memberReportByLastYear(@Param("year") String year, @Param("monthfirst") String monthfirst, @Param("monthend") String monthend);



    @Select("select count(tm.ID) as memberCounts, concat(year(rd.fdate),'-',rd.fmonth) as yearAndMonth  " +
            "     from (select * from rpt_date rd where rd.fdate between #{begin} and #{end}) rd left outer join t_member  tm " +
            "     on rd.fdate = tm.REGTIME " +
            "     group by yearAndMonth order by rd.fdate;")
    List<Map> memberReportByLastYear(@Param("begin") String begin, @Param("end") String end);


}
